﻿using System.Threading.Tasks;
using System;
using Microsoft.EntityFrameworkCore;
using Oracle.ManagedDataAccess.Client;
using Microsoft.AspNetCore.Http;
using Simple.Services.System;
using System.Data;
using OfficeOpenXml;
using System.Net;
using System.Net.Http.Headers;
using Simple.Services.System.QHSEDanger.Models.AnalysisInput;
using Simple.Services.System.QHSEViolationCategory.Models;
using System.Globalization;
using System.ComponentModel.Design;
using System.Collections;
using System.Security.Cryptography;
using Microsoft.AspNetCore.Mvc.RazorPages;
using System.Drawing.Printing;
using static Microsoft.EntityFrameworkCore.DbLoggerCategory;
using System.Reactive;
using Minio.DataModel;
using System.Reflection;
using OfficeOpenXml.Style;

namespace Simple.Services;

public class QHSEOverLappingViolationService
{
    private readonly SimpleDbContext _context;
    private readonly CacheService _cacheService;
    private readonly QHSEOrganizationService _organizationService;
    private readonly IQhseFileService _qhseFileService;

    public QHSEOverLappingViolationService(SimpleDbContext context, CacheService cacheService, QHSEOrganizationService organizationService,IQhseFileService qhseFileService)
    {
        _context = context;
        _cacheService = cacheService;
        _organizationService = organizationService;
        _qhseFileService = qhseFileService;
    }
    public async Task<List<QHSEOverLappingViolationModel>> GetBySqlAsync(IdInputModel id)
    {
        var violations = await _context.Set<QHSESysOverLappingViolation>().FromSqlRaw(id.Id).ToListAsync();
        var models = MapperHelper.Map<List<QHSEOverLappingViolationModel>>(violations);
        return models;
    }
    public async Task<PageResultModel<QHSEOverLappingViolationModel>> GetPageAsync(OverLappingViolationPageInputModel input)
    {

        var result = new PageResultModel<QHSEOverLappingViolationModel>();
        if (!string.IsNullOrEmpty(input.OrganizationName) || !string.IsNullOrEmpty(input.UserName)|| input.TimeOn!= null || input.TimeEnd != null)
        {
                var query = _context.Set<QHSESysOverLappingViolation>().AsNoTracking().AsQueryable();

                if (!string.IsNullOrEmpty(input.OrganizationName))
                {
                    var org = await _context.Set<QHSESysOrganization>().FindAsync(input.OrganizationId);
                    if (org != null)
                    {
                        var escapedNamePath = EscapeLikePattern(org.NamePath);
                        query = query
                            .Join(_context.Set<QHSESysOrganization>(),
                                violation => violation.OrganizationId,
                                organization => organization.OrganizationId,
                                (violation, organization) => new { Violation = violation, Organization = organization })
                            .Where(joinResult => EF.Functions.Like(joinResult.Organization.NamePath, $"{escapedNamePath}%", "/"))
                            .Select(joinResult => joinResult.Violation);
                    }
                }


                if (!string.IsNullOrEmpty(input.UserName))
                {
                    query = query.Where(u => EF.Functions.Like(u.UserName, $"%{input.UserName}%"));
                }

                if (input.TimeOn != null && input.TimeEnd != null)
                {
                    query = query.Where(u => u.CheckDate >= input.TimeOn && u.CheckDate <= input.TimeEnd);
                }
            if (input.ContractingUnitId != null)
            {
                if (input.ContractingUnitId.Count != 0)
                {
                    query = query.Where(u => string.Join("-", input.ContractingUnitId).Contains(u.ContractingUnitId));
                }
            }
            var totalRows = await query.CountAsync();

                var violations = await query
                                    .OrderBy(u => u.Check_Type)
                                    .Join(_context.Set<QHSESysOrganization>(), // Join with QHSESysOrganization table
                                        violation => violation.OrganizationId,
                                        organization => organization.OrganizationId,
                                        (violation, organization) => new
                                        {
                                            Violation = violation,
                                            OrganizationName = organization.NamePath
                                        })
                                    .Select(joinResult => new QHSEOverLappingViolationModel
                                    {
                                        QhseXyViolationId = joinResult.Violation.QhseXyViolationId,
                                        Check_Type = joinResult.Violation.Check_Type,
                                        UserId = joinResult.Violation.UserId,
                                        UserName = joinResult.Violation.UserName,
                                        CheckDate = (DateTime)joinResult.Violation.CheckDate,
                                        OrganizationId = joinResult.Violation.OrganizationId,
                                        OrganizationName = joinResult.OrganizationName, // 这里直接赋值
                                        Description = joinResult.Violation.Description,
                                        WorkItem = joinResult.Violation.WorkItem,
                                        ViolationUserId = joinResult.Violation.ViolationUserId,
                                        ViolationUserName = joinResult.Violation.ViolationUserName,
                                        ViolationType = joinResult.Violation.ViolationType,
                                        ContractingUnitId = joinResult.Violation.ContractingUnitId,
                                        ContractingUnitName = joinResult.Violation.ContractingUnitName,
                                        ViolationUserDepart = joinResult.Violation.ViolationUserDepart,
                                        Position = joinResult.Violation.Position,
                                        Erp = joinResult.Violation.Erp

                                    })
                                    .OrderByDescending(u=>u.CheckDate)
                                    .Page(input.PageNo, input.PageSize)
                                    .ToListAsync();


                var models = MapperHelper.Map<List<QHSEOverLappingViolationModel>>(violations);


                result = new PageResultModel<QHSEOverLappingViolationModel>
                {
                    TotalRows = totalRows,
                    Rows = models,
                    PageNo = input.PageNo,
                    PageSize = input.PageSize
                };
            result.SetPage(input);
            result.CountTotalPage();
        }
        return result;

    }
    private static string EscapeLikePattern(string pattern)
    {
        return pattern.Replace("'", "''").Replace("/", "//").Replace("_", "/_").Replace("%", "/%");
    }

    public async Task<QHSEOverLappingViolationModel> QueryDetailAsync(string Vid)
    {
        var violation = new QHSESysOverLappingViolation();
        violation = await _context.Set<QHSESysOverLappingViolation>().FindAsync(Vid);
        var model = MapperHelper.Map<QHSEOverLappingViolationModel>(violation);
        if (model.Pic != null)
        {
            var files = await utilService.GetFilesAsync(model.Pic,_qhseFileService);
            model.Files = files;
        }
        return model;
    }
    public async Task<string> AddAsync(QHSEOverLappingViolationModel model)
    {
        using (var transaction = await _context.Database.BeginTransactionAsync())
        {
            try
            {
                if (model.Files.Count != 0)
                {
                    var hashList = new List<string>(); // 用于存储每个文件的哈希值

                    foreach (var file in model.Files)
                    {
                        using (var sha256 = SHA256.Create())
                        {
                            byte[] byteArray = Convert.FromBase64String(file.Base64Content);
                            byte[] hash = sha256.ComputeHash(byteArray);
                            string hashString = BitConverter.ToString(hash).Replace("-", "").ToLower();
                            hashList.Add(hashString);
                        }
                    }
                    model.ImageHash = string.Join("+", hashList); // 拼接哈希值字符串
                    var msg = await utilService.UploadFilesAsync("VIOLATION", model.Files, model.UserId,_qhseFileService);
                    model.Pic = msg.LastOrDefault().ArgumentName ;
                }
                Guid guid = Guid.NewGuid();
                string guidString = RemoveHyphens(guid);
                if (string.IsNullOrEmpty(model.QhseXyViolationId))
                {
                    model.QhseXyViolationId = guidString;
                }
                if (string.IsNullOrEmpty(model.Age))
                {
                    model.Age = "备用";
                }
                if (string.IsNullOrEmpty(model.AgeCode))
                {
                    model.AgeCode = "备用";
                }
                if (string.IsNullOrEmpty(model.Erp))
                {
                    model.Erp = "备用";
                }
                if (string.IsNullOrEmpty(model.WorkYears))
                {
                    model.WorkYears = "备用";
                }
                if (string.IsNullOrEmpty(model.WorkYearsCode))
                {
                    model.WorkYearsCode = "备用";
                }
                model.ViolationUserId = model.ViolationUserName;
                var violation = MapperHelper.Map<QHSESysOverLappingViolation>(model);
                
                await _context.AddAsync(violation);
                await _context.SaveChangesAsync();


                await _cacheService.ClearOverLappingCountCacheAsync();

                await transaction.CommitAsync(); // Commit the transaction

                if (!string.IsNullOrEmpty(model.QhseXyViolationId))
                {
                    return model.QhseXyViolationId;
                }
                else
                {
                    return null; // or throw an exception, depending on your requirements
                }
            }
            catch (Exception)
            {
                await transaction.RollbackAsync(); // Rollback the transaction if an exception occurs
                throw;
            }
        }
    }
    public static bool IsEmpty(object obj)
    {
        Type type = obj.GetType();
        PropertyInfo[] properties = type.GetProperties();

        foreach (PropertyInfo property in properties)
        {
            object value = property.GetValue(obj);
            var s = property.ToString();
            if (property.ToString() != "System.String QhseXyViolationId" && property.ToString() != "System.String Pic" &&  property.ToString() != "System.String ImageHash" && (value == null || string.IsNullOrEmpty(value.ToString())))
            {
                return true;
            }
        }

        return false;
    }
    static string RemoveHyphens(Guid guid)
    {
        return guid.ToString().Replace("-", ""); // 通过 Replace 函数移除连字符 "-"
    }
    public async Task<bool> IsImageDuplicate(string newImageBase64)
    {
        if (string.IsNullOrEmpty(newImageBase64))
        {
            return false;
        }

        using (var sha256 = SHA256.Create())
        {
            byte[] byteArray = Convert.FromBase64String(newImageBase64);
            byte[] hash = sha256.ComputeHash(byteArray);
            string hashString = BitConverter.ToString(hash).Replace("-", "").ToLower();

            DateTime oneYearAgo = DateTime.Today.AddYears(-1);

            var duplicate = await _context.Set<QHSESysXyViolation>()
                .Where(violation => violation.CheckDate >= oneYearAgo && violation.ImageHash.Contains(hashString))
                .AnyAsync();

            return duplicate;
        }
    }

    public async Task<int> UpdateAsync(QHSEOverLappingViolationModel model)
    {
        var violation = await _context.Set<QHSESysOverLappingViolation>()
            .FindAsync(model.QhseXyViolationId);

        if (violation == null)
        {
            throw AppResultException.Status404NotFound("找不到违章，更新失败");
        }

        if (model.Files.Count != 0)
        {
            var fileUploadItems = model.Files.Select(file => new FileUploadItem
            {
                Name = file.Name,
                Base64Content = file.Base64Content
            }).ToList();
            var msgs = await utilService.UploadFilesAsync("VIOLATION",fileUploadItems, violation.UserId,_qhseFileService);
            bool uploadSuccess = msgs.All(msg => msg.FileStatus == 1);

            if (uploadSuccess)
            {
                if (!string.IsNullOrEmpty(violation.Pic))
                {
                    List<string> namesList = violation.Pic.Split(", ")
                                     .Select(name => name.Trim())  // 去除每个名字前后的空格
                                     .ToList();
                    foreach(var name in namesList)
                    {
                        _qhseFileService.RemoveAsync("ssew", name);
                    }
                }

                model.Pic = msgs.LastOrDefault()?.FileName;
            }
        }
        else
        {
            if (!string.IsNullOrEmpty(violation.Pic))
            {
                    List<string> namesList = violation.Pic.Split(", ")
                                            .Select(name => name.Trim())  // 去除每个名字前后的空格
                                            .ToList();
                    foreach (var name in namesList)
                    {
                        _qhseFileService.RemoveAsync("ssew", name);
                    }
            }

            model.Pic = null;
        }
        if (string.IsNullOrEmpty(model.Age))
        {
            model.Age = "备用";
        }
        if (string.IsNullOrEmpty(model.AgeCode))
        {
            model.AgeCode = "备用";
        }
        if (string.IsNullOrEmpty(model.Erp))
        {
            model.Erp = "备用";
        }
        if (string.IsNullOrEmpty(model.WorkYears))
        {
            model.WorkYears = "备用";
        }
        if (string.IsNullOrEmpty(model.WorkYearsCode))
        {
            model.WorkYearsCode = "备用";
        }
        MapperHelper.Map<QHSEOverLappingViolationModel, QHSESysOverLappingViolation>(model, violation);
        _context.Update(violation);
        int ret = await _context.SaveChangesAsync();

        if (ret == 0)
        {
            throw AppResultException.Status200OK("更新记录数为0");
        }

        return ret;
    }
    public async Task<int> DeleteAsync(IEnumerable<string> ids)
    {
        var violations = await _context.Set<QHSESysOverLappingViolation>()
            .Where(a => ids.Contains(a.QhseXyViolationId))
            .ToListAsync();
        foreach(var violation in violations)
        {
            if (!string.IsNullOrEmpty(violation.Pic))
            {
                List<string> namesList = violation.Pic.Split(", ")
                                    .Select(name => name.Trim())  // 去除每个名字前后的空格
                                    .ToList();
                foreach (var name in namesList)
                {
                    _qhseFileService.RemoveAsync("ssew", name);
                }
            }
            
        }
        _context.RemoveRange(violations);
        await _cacheService.ClearOverLappingCountCacheAsync();
        int ret = await _context.SaveChangesAsync();
        return ret;
    }
    public async Task<byte[]> UploadExcel(OverLappingViolationPageInputModel model)
    {
        List<QHSEOverLappingViolationModel> allData = new List<QHSEOverLappingViolationModel>();
        int totalPage = 0;

        // 获取总页数,一次取1000条数据
        model.PageSize = 1000;
        PageResultModel<QHSEOverLappingViolationModel> pageResultModel = await GetPageAsync(model);
        totalPage = pageResultModel.TotalPage;

        for (int pageNo = 1; pageNo <= totalPage; pageNo++)
        {
            // 设置当前页码
            model.PageNo = pageNo;

            // 执行分页查询，获取当前页的数据
            pageResultModel = await GetPageAsync(model);
            List<QHSEOverLappingViolationModel> currentPageData = pageResultModel.Rows;

            // 将当前页的数据添加到总数据集合
            allData.AddRange(currentPageData);
        }

        // 设置 ExcelPackage 的 LicenseContext
        ExcelPackage.LicenseContext = LicenseContext.NonCommercial;
        // 创建一个新的Excel包
        using (ExcelPackage package = new ExcelPackage())
        {
            ExcelWorksheet worksheet = package.Workbook.Worksheets.Add("Sheet1");
            // 合并单元格
            ExcelRange titleCell = worksheet.Cells["A1:V1"];
            titleCell.Merge = true;
            // 设置标题内容
            titleCell.Value = model.TimeOn.Year.ToString() + "年" + model.TimeOn.Month.ToString() + "月" + "作业现场相关方违章统计表";
            // 设置标题样式
            titleCell.Style.Font.Size = 20;
            titleCell.Style.Font.Bold = true;
            titleCell.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;

            // 写入表头并设置字体加粗
            for (int col = 0; col <= 28; col++)
            {
                ExcelRange headerCell = worksheet.Cells[2, col+1];
                headerCell.Value = GetHeaderTitle(col);
                headerCell.Style.Font.Bold = true;
            }

            // 写入数据
            for (int row = 0; row < allData.Count; row++)
            {
                QHSEOverLappingViolationModel data = allData[row];
                worksheet.Cells[row + 3, 1].Value = data.CheckDate.ToString();
                worksheet.Cells[row + 3, 2].Value = data.WorkItem;
                worksheet.Cells[row + 3, 3].Value = data.OrganizationName;
                worksheet.Cells[row + 3, 4].Value = data.Description;
                worksheet.Cells[row + 3, 5].Value = data.ViolationUserName;
                worksheet.Cells[row + 3, 6].Value = data.Position;
                worksheet.Cells[row + 3, 7].Value = data.ViolationType;
                worksheet.Cells[row + 3, 8].Value = data.ViolationUserDepart;
                worksheet.Cells[row + 3, 9].Value = data.ContractingUnitName;
                worksheet.Cells[row + 3, 10].Value = data.Erp;
            }

            // 设置列宽
            for (int i = 1; i <= 28; i++)
            {
                worksheet.Column(i).AutoFit();
            }

            // 生成Excel文件的字节数组
            byte[] fileBytes = package.GetAsByteArray();
            return fileBytes;
        }
    }

    // Custom method to get header titles based on column index
    private string GetHeaderTitle(int columnIndex)
    {
        switch (columnIndex)
        {
            case 0:
                return "日期";
            case 1:
                return "井号（项目）";
            case 2:
                return "川庆公司所属单位";
            case 3:
                return "违章情况";
            case 4:
                return "违章人员姓名";
            case 5:
                return "违章人员岗位";
            case 6:
                return "违章性质";
            case 7:
                return "违章人员所属甲方相关方公司";
            case 8:
                return "项目所属甲方处级发包单位";
            case 9: return "违章人员员工编号";
            default: return string.Empty;
        }
    }

    public async Task<PageResultModel<OverLappingVadPageResultModel>> GetOverLappingTotalAsync(OverLappingVadPageInputModel input)
    {
        var result = new PageResultModel<OverLappingVadPageResultModel>();
        if(await _cacheService.GetOverLappingCountAsync(input) != null)
        {
            return await _cacheService.GetOverLappingCountAsync(input);
        }
        var secondaryUnit = await _organizationService.GetSon("00");
        secondaryUnit = secondaryUnit.OrderBy(u => u.DataSort).ThenBy(u => u.OrganizationName).ToList();
        var vquery = _context.Set<QHSESysOverLappingViolation>().AsNoTracking().Where(u => u.CheckDate >= input.TimeOn && u.CheckDate <= input.TimeEnd).AsQueryable();
        var dquery = _context.Set<QHSESysOverLappingDanger>().AsNoTracking().Where(u => u.CheckDate >= input.TimeOn && u.CheckDate <= input.TimeEnd).AsQueryable();
        if (secondaryUnit != null)
        {
            List<OverLappingVadPageResultModel> resultModels = new List<OverLappingVadPageResultModel>();
            int totalViolation = 0;
            int totalDanger = 0;
            int totalVad = 0;
            foreach (var unit in secondaryUnit)
            {
                OverLappingVadPageResultModel resultModel = new OverLappingVadPageResultModel();
                var escapedNamePath = EscapeLikePattern(unit.NamePath);
                var violationCount = await vquery.Join(_context.Set<QHSESysOrganization>(),
                        violation => violation.CheckOrganizationId,
                        organization => organization.OrganizationId,
                        (violation, organization) => new { Violation = violation, Organization = organization })
                    .Where(joinResult => EF.Functions.Like(joinResult.Organization.NamePath, $"{escapedNamePath}%", "/")).CountAsync();
                var dangerCount = await dquery.Join(_context.Set<QHSESysOrganization>(),
                                        violation => violation.CheckOrganizationId,
                                        organization => organization.OrganizationId,
                                        (violation, organization) => new { Violation = violation, Organization = organization })
                                    .Where(joinResult => EF.Functions.Like(joinResult.Organization.NamePath, $"{escapedNamePath}%", "/")).CountAsync();
                var totalCount = violationCount + dangerCount;
                totalDanger += dangerCount;
                totalViolation += violationCount;
                totalVad += totalCount;
                resultModel.ViolationCount = violationCount;
                resultModel.DangerCount = dangerCount;
                resultModel.TotalCount = totalCount;
                resultModel.OrganizationName = unit.OrganizationName;
                resultModels.Add(resultModel);
            }

            OverLappingVadPageResultModel totalModel = new OverLappingVadPageResultModel();
            totalModel.OrganizationName = "总计";
            totalModel.ViolationCount = totalViolation;
            totalModel.DangerCount = totalDanger;
            totalModel.TotalCount = totalVad;

            resultModels.Add(totalModel);
            result.TotalRows = secondaryUnit.Count();
            result.PageNo = input.PageNo;
            result.PageSize = input.PageSize;
            result.Rows = resultModels;
            result.TotalPage = result.CountTotalPage();
        }
        await _cacheService.SetOverLappingCountAsync(input, result);
        return result;
            
    }
}
